package com.isoft.mapper;

import com.isoft.entity.DfmeBoreEntity;
import org.apache.ibatis.annotations.*;

@Mapper
public interface DfmeBoreMapper {

    @Insert("INSERT INTO MSG_META(SENDER, RECEIVER, SEQUENCE_NUMBER, DATE_TIME, MSG_TYPE, SUB_TYPE) " +
            "VALUES(" +
            "#{meta.sender,jdbcType=VARCHAR}, " +
            "#{meta.receiver,jdbcType=VARCHAR}, " +
            "#{meta.sequenceNumber,jdbcType=NUMERIC}, " +
            "TO_TIMESTAMP(#{meta.dateTime,jdbcType=VARCHAR}, 'YYYYMMDDHH24MISS'), " +
            "#{meta.type,jdbcType=VARCHAR}, " +
            "#{meta.subType,jdbcType=VARCHAR})")
    @Options(useGeneratedKeys = true, keyProperty = "meta.msgId", keyColumn = "MSG_ID")
    void insertMeta(DfmeBoreEntity entity);

    @Insert("MERGE INTO FLIGHT_BASE t " +
            "USING (SELECT #{dflt.flightId} as FLIGHT_ID, #{dflt.flightAttribute} as FLIGHT_ATTRIBUTE, " +
            "#{dflt.flightFileId} as FLIGHT_FILE_ID, #{dflt.flightIdentifier} as FLIGHT_IDENTIFIER, " +
            "CASE WHEN #{dflt.status,jdbcType=VARCHAR} IN ('NORMAL', 'DELAYED', 'CANCELLED', 'BOARDING', 'DEPARTED', 'ALTERNATE', 'RETURNED', 'BOARDING_COMPLETED') " +
            "THEN #{dflt.status} ELSE 'BOARDING' END as STATUS, " +
            "#{dflt.internalStatus} as INTERNAL_STATUS, " +
            "#{dflt.manualStatus} as MANUAL_STATUS, " +
            "#{dflt.manualInternalStatus} as MANUAL_INTERNAL_STATUS FROM DUAL) s " +
            "ON (t.FLIGHT_ID = s.FLIGHT_ID) " +
            "WHEN MATCHED THEN " +
            "UPDATE SET t.FLIGHT_ATTRIBUTE = s.FLIGHT_ATTRIBUTE, t.FLIGHT_FILE_ID = s.FLIGHT_FILE_ID, " +
            "t.FLIGHT_IDENTIFIER = s.FLIGHT_IDENTIFIER, t.STATUS = s.STATUS, " +
            "t.INTERNAL_STATUS = s.INTERNAL_STATUS, t.MANUAL_STATUS = s.MANUAL_STATUS, " +
            "t.MANUAL_INTERNAL_STATUS = s.MANUAL_INTERNAL_STATUS " +
            "WHEN NOT MATCHED THEN " +
            "INSERT (FLIGHT_ID, FLIGHT_ATTRIBUTE, FLIGHT_FILE_ID, FLIGHT_IDENTIFIER, " +
            "STATUS, INTERNAL_STATUS, MANUAL_STATUS, MANUAL_INTERNAL_STATUS) " +
            "VALUES (s.FLIGHT_ID, s.FLIGHT_ATTRIBUTE, s.FLIGHT_FILE_ID, s.FLIGHT_IDENTIFIER, " +
            "s.STATUS, s.INTERNAL_STATUS, s.MANUAL_STATUS, s.MANUAL_INTERNAL_STATUS)")
    int upsertFlightBase(DfmeBoreEntity entity);

    @Insert("INSERT INTO BOARDING_TIME(FLIGHT_ID, BOARDING_TYPE, BOARDING_TIME) " +
            "VALUES(" +
            "#{dflt.flightId,jdbcType=NUMERIC}, " +
            "'BORT', " +
            "TO_TIMESTAMP(#{dflt.boardingTime,jdbcType=VARCHAR}, 'YYYYMMDDHH24MISS'))")
    @Options(useGeneratedKeys = true, keyProperty = "dflt.timeId", keyColumn = "TIME_ID")
    int insertBoardingTime(DfmeBoreEntity entity);

    @Update("UPDATE MSG_META SET " +
            "SENDER = #{meta.sender,jdbcType=VARCHAR}, " +
            "RECEIVER = #{meta.receiver,jdbcType=VARCHAR}, " +
            "DATE_TIME = TO_TIMESTAMP(#{meta.dateTime,jdbcType=VARCHAR}, 'YYYYMMDDHH24MISS'), " +
            "MSG_TYPE = #{meta.type,jdbcType=VARCHAR}, " +
            "SUB_TYPE = #{meta.subType,jdbcType=VARCHAR} " +
            "WHERE MSG_ID = #{meta.msgId,jdbcType=NUMERIC}")
    int updateMeta(DfmeBoreEntity entity);

    @Select("SELECT COUNT(*) FROM MSG_META " +
            "WHERE SEQUENCE_NUMBER = #{sequenceNumber,jdbcType=NUMERIC} " +
            "AND MSG_TYPE = #{type,jdbcType=VARCHAR} " +
            "AND SUB_TYPE = #{subType,jdbcType=VARCHAR}")
    int countMeta(@Param("sequenceNumber") Long sequenceNumber,
                  @Param("type") String type,
                  @Param("subType") String subType);

    @Insert("INSERT INTO FLIGHT_IRREGULARITY(" +
            "FLIGHT_ID, ABNORMAL_STATUS, ABNORMAL_REASON, " +
            "INTERNAL_STATUS, INTERNAL_REASON, MANUAL_ABNORMAL_STATUS, MANUAL_ABNORMAL_REASON, " +
            "MANUAL_INTERNAL_STATUS, MANUAL_INTERNAL_REASON) " +
            "VALUES(" +
            "#{dflt.flightId,jdbcType=NUMERIC}, " +
            "'BOR', " +
            "NULL, " +
            "'BOR', " +
            "NULL, " +
            "NULL, NULL, NULL, NULL)")
    int insertIrregularity(DfmeBoreEntity entity);
}